
# Workload trend chart并打印某几个月的工时分项汇总数据

import pymysql
import matplotlib.pyplot as plt

def calculate_workload_summary(cursor):
    """计算并返回2025年4-6月的工时分类汇总数据"""
    # 定义要查询的月份和对应的中文名称
    months = [(2025, 7, "7月"), (2025, 8, "8月"), (2025, 9, "9月")]
    # 定义要统计的工时分类及其对应的SQL查询条件
    categories = {
        "Full_ALT": "test_category='Full ALT' and special !='s'",
        "ALT_Eval": "test_category='ALT Eval' and special !='s'",
        "DVT": "test_category='DVT' and special !='s'",
        "DVT_Eval": "test_category='DVT Eval' and special !='s'",
        "CALT": "test_category='CALT'",
        "CDVT": "test_category='CDVT' and special !='s'",
        "ATT": "test_category='ATT' and special !='s'",
        "TMO": "test_category='TMO' and special !='s'",
        "FCNT": "test_category='FCNT' and special !='s'",
        "others": """test_category!='TMO' and test_category!='ATT' and test_category!='CDVT' 
                  and test_category!='DVT Eval' and test_category!='CALT' and test_category!='DVT' 
                  and test_category!='ALT Eval' and test_category!='Full ALT' and test_category!='FCNT' 
                  and test_category!='Operation' and special !='s'"""
    }
    
    # 存储每个月各类别的工时数据
    monthly_summaries = {}
    
    # 查询每个月各类别的工时
    for year, month, month_name in months:
        month_data = {}
        for cat_name, condition in categories.items():
            cursor.execute(f'''
                select sum(test_hour*coefficient) 
                from assignment 
                where month(date)=%s and year(date)=%s and {condition}
            ''', (month, year))
            result = cursor.fetchone()
            month_data[cat_name] = result[0] if result[0] is not None else 0
        monthly_summaries[month_name] = month_data
    
    # 计算三个月的总计
    total_summary = {}
    for cat_name in categories.keys():
        total = sum(monthly_summaries[m][cat_name] for m in monthly_summaries)
        total_summary[cat_name] = total
    
    return monthly_summaries, total_summary

def workload_chart(ip_address):     
    conn = pymysql.connect(host=ip_address,user='labuser',db='exceltodb',password='123456',port=3306,charset='utf8')
    cursor = conn.cursor()

    # 取出当前最大的年和月, 显示前24个月的图形
    cursor.execute('select max(date) from assignment')
    maxdate=cursor.fetchone()[0]
    start_year=maxdate.year-1
    end_year=maxdate.year+1
    current_month=maxdate.month
    print(start_year,end_year)
    print(current_month)

    mmyy=[]
    fullalt=[]
    alteval=[]
    dvt=[]
    dvteval=[]
    calt=[]
    cdvt=[]
    att=[]
    tmo=[]
    fcnt=[]
    others=[]

    for y in range(start_year,end_year):    
        if y==start_year:
            monthlist=[]
            for i in range(current_month,13):
                monthlist.append(i)
        if y==start_year+1:
            monthlist=[]
            for i in range(1,13):
                monthlist.append(i)  
        if y==end_year-1:
            monthlist=[]
            for i in range(1,current_month+1):
                monthlist.append(i)

        for m in monthlist:
            mm_yy=str(y)[0:4]+'_'+str(m)
            mmyy.append(mm_yy)
            
            # 查询各类别的工时数据（与原代码相同）
            cursor.execute('''select sum(test_hour*coefficient),date from assignment where month(date)=%s and year(date)=%s
            and test_category='Full ALT' and special !='s' ''',(m,y))
            summary=cursor.fetchone()
            fullalt.append(summary[0] if summary[0] is not None else 0)
    
            cursor.execute('''select sum(test_hour*coefficient),date from assignment where month(date)=%s and year(date)=%s
            and test_category='ALT Eval' and special !='s' ''',(m,y))
            summary=cursor.fetchone()
            alteval.append(summary[0] if summary[0] is not None else 0)
                
            cursor.execute('''select sum(test_hour*coefficient),date from assignment where month(date)=%s and year(date)=%s
            and test_category='DVT' and special !='s' ''',(m,y))
            summary=cursor.fetchone()
            dvt.append(summary[0] if summary[0] is not None else 0)

            cursor.execute('''select sum(test_hour*coefficient),date from assignment where month(date)=%s and year(date)=%s
            and test_category='DVT Eval'and special !='s' ''',(m,y))
            summary=cursor.fetchone()
            dvteval.append(summary[0] if summary[0] is not None else 0)

            cursor.execute('''select sum(test_hour*coefficient),date from assignment where month(date)=%s and year(date)=%s
            and test_category='CALT' ''',(m,y))
            summary=cursor.fetchone()
            calt.append(summary[0] if summary[0] is not None else 0)

            cursor.execute('''select sum(test_hour*coefficient),date from assignment where month(date)=%s and year(date)=%s
            and test_category='CDVT' and special !='s' ''',(m,y))
            summary=cursor.fetchone()
            cdvt.append(summary[0] if summary[0] is not None else 0)

            cursor.execute('''select sum(test_hour*coefficient),date from assignment where month(date)=%s and year(date)=%s
            and test_category='ATT' and special !='s' ''',(m,y))
            summary=cursor.fetchone()
            att.append(summary[0] if summary[0] is not None else 0)

            cursor.execute('''select sum(test_hour*coefficient),date from assignment where month(date)=%s and year(date)=%s
            and test_category='TMO'and special !='s' ''',(m,y))
            summary=cursor.fetchone()
            tmo.append(summary[0] if summary[0] is not None else 0)

            cursor.execute('''select sum(test_hour*coefficient),date from assignment where month(date)=%s and year(date)=%s
            and test_category='FCNT' and special !='s' ''',(m,y))
            summary=cursor.fetchone()
            fcnt.append(summary[0] if summary[0] is not None else 0)

            cursor.execute('''select sum(test_hour*coefficient),date from assignment where month(date)=%s and year(date)=%s
            and test_category!='TMO' and test_category!='ATT' and test_category!='CDVT' and test_category!='DVT Eval'
            and test_category!='CALT' and test_category!='DVT' and test_category!='ALT Eval' and test_category!='Full ALT' 
            and test_category!='FCNT' and test_category!='Operation' and special !='s' ''',(m,y))
            summary=cursor.fetchone()
            others.append(summary[0] if summary[0] is not None else 0)

    # 计算2025年7-9月的工时分类汇总
    monthly_summaries, total_summary = calculate_workload_summary(cursor)
    
    # 打印工时分类汇总数据
    print("\n\n===== 2025年7-9月工时分类汇总 =====")
    print("{:<10} {:<10} {:<10} {:<10} {:<10}".format("类别", "7月", "8月", "9月", "总计"))
    print("-" * 50)
    
    # 按照图例顺序输出
    category_order = ["Full_ALT", "ALT_Eval", "DVT", "DVT_Eval", "CALT", "CDVT", "ATT", "TMO", "FCNT", "others"]
    for cat in category_order:
        cat_name = cat.replace("_", " ")  # 将下划线替换为空格，与图例一致
        print("{:<10} {:<10.1f} {:<10.1f} {:<10.1f} {:<10.1f}".format(
            cat_name, 
            monthly_summaries["7月"][cat],
            monthly_summaries["8月"][cat],
            monthly_summaries["9月"][cat],
            total_summary[cat]
        ))
    print("-" * 50)
    print("{:<10} {:<10.1f} {:<10.1f} {:<10.1f} {:<10.1f}".format(
        "总计",
        sum(monthly_summaries["7月"].values()),
        sum(monthly_summaries["8月"].values()),
        sum(monthly_summaries["9月"].values()),
        sum(total_summary.values())
    ))
    
    # 关闭数据库连接
    cursor.close()
    conn.close()

    # 堆叠柱形图数据处理（保持原堆叠顺序）
    x = mmyy

    y10=fullalt
    y9=alteval
    y8=dvt
    y7=dvteval
    y6=calt
    y5=cdvt
    y4=att
    y3=tmo
    y2=fcnt
    y1=others

    # 计算原堆叠顺序的底部位置（与原代码相同）
    d3=[];d4=[];d5=[];d6=[];d7=[];d8=[];d9=[];d10=[];d11=[]
    for i in range(len(y1)):    
        sum3 = y1[i] + y2[i]
        sum4 = sum3 + y3[i]
        sum5 = sum4 + y4[i]
        sum6 = sum5 + y5[i]
        sum7 = sum6 + y6[i]
        sum8 = sum7 + y7[i]
        sum9 = sum8 + y8[i]
        sum10= sum9 + y9[i]
        sum11= sum10 + y10[i]
        d3.append(sum3);d4.append(sum4);d5.append(sum5);d6.append(sum6)
        d7.append(sum7);d8.append(sum8);d9.append(sum9);d10.append(sum10);d11.append(sum11)

    # 创建画布
    fig = plt.figure(figsize=(7, 5), dpi=100)
    ax = fig.add_subplot(111)

    # 绘制柱形图（保持原堆叠顺序不变）
    bar1 = ax.bar(x, y1, width=0.5, color='C3', label='others')
    bar2 = ax.bar(x, y2, width=0.5, bottom=y1, color='C4', label='FCNT')
    bar3 = ax.bar(x, y3, width=0.5, bottom=d3, color='C5', label='TMO')
    bar4 = ax.bar(x, y4, width=0.5, bottom=d4, color='C6', label='ATT')
    bar5 = ax.bar(x, y5, width=0.5, bottom=d5, color='C7', label='CDVT')
    bar6 = ax.bar(x, y6, width=0.5, bottom=d6, color='C8', label='CALT')
    bar7 = ax.bar(x, y7, width=0.5, bottom=d7, color='C9', label='DVT_Eval')
    bar8 = ax.bar(x, y8, width=0.5, bottom=d8, color='C10', label='DVT')
    bar9 = ax.bar(x, y9, width=0.5, bottom=d9, color='C11', label='ALT_Eval')
    bar10 = ax.bar(x, y10, width=0.5, bottom=d10, color='C12', label='Full_ALT')

    # 设置样式（核心修改：提取图例句柄并反转顺序）
    ax.grid(axis='y', linestyle='--')
    ax.set_axisbelow(True)
    ax.spines[['right', 'top']].set_color('C7')

    # 获取图例句柄和标签，反转顺序后再生成图例
    handles, labels = ax.get_legend_handles_labels()
    ax.legend(
        handles[::-1],  # 反转句柄顺序
        labels[::-1],   # 反转标签顺序
        fontsize=6, 
        ncol=1, 
        loc='upper left', 
        bbox_to_anchor=(0.99, 0.95)
    )
    
    # 柱子上面显示总数值
    for x_val, y_val in zip(x, d11):        
        plt.text(x_val, y_val, f'{y_val:.0f}', ha='center', va='bottom', fontsize=6)

    plt.xticks(fontsize=7, rotation=45)
    plt.title('TCC Reliability Workload Trend Chart', fontsize=10)
    
    plt.show()

if __name__=="__main__":    
    # ip_address='localhost'
    ip_address='10.114.183.17'
    workload_chart(ip_address)